using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAOLayer
{
    public class DoanhThuDao
    {
        public static DataTable ThongKeLoaiPhongTheoThang(int thang,int malp)
        {
            DataTable dt = new DataTable();
            OleDbConnection cn = Database.ConnectionData();
            //'B3: Tao chuoi strSQL thao tac CSDL
            String strSQL = " Select cthd.ThanhTien";
            strSQL += " From PHONG p, PHIEUTHUE pt , HOADON hd , CHITIETHOADON cthd Where pt.MaPhong = p.MaPhong and cthd.MaPhieuThue = pt.MaPhieuThue ";
            strSQL += " and hd.MaHoaDon = cthd.MaHoaDon and p.MaLoaiPhong = ?";
            strSQL += " and Month(hd.NgayThanhToan) = ? ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaLoaiPhong", OleDbType.Integer);
            cmd.Parameters[("@MaLoaiPhong")].Value = malp;
            cmd.Parameters.Add("@Thang", OleDbType.Integer);
            cmd.Parameters[("@Thang")].Value = thang;

            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }
        public static void Them(DoanhThuDto p)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn = Database.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL = "Insert into DOANHTHU ( Thang,MaLoaiPhong,DoanhThu,TyLe ) values(?,?,?, ?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Thang", OleDbType.Integer);
            cmd.Parameters["@Thang"].Value = p.Thang;
            cmd.Parameters.Add("@MaLoaiPhong", OleDbType.Integer);
            cmd.Parameters["@MaLoaiPhong"].Value = p.MaLoaiPhong;
            cmd.Parameters.Add("@DoanhThu", OleDbType.Double);
            cmd.Parameters["@DoanhThu"].Value = p.DoanhThu;
            cmd.Parameters.Add("@TyLe", OleDbType.Double);
            cmd.Parameters["@TyLe"].Value = p.TyLe;
            cmd.ExecuteNonQuery();

            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            p.MaDoanhThu = Convert.ToInt32(cmd.ExecuteScalar());
            // B5: Dong ket noi CSDL
            cn.Close();
        }

    }
}
